/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */
package com.ruoyi.web.controller.monitor;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import java.io.*;
import java.nio.charset.StandardCharsets;

/**
 * This example shows how to display a spreadsheet in HTML using the classes for
 * spreadsheet display.
 * 注意html style 需要加上css,否则格式和excel不对
 <style type="text/css">
 .excelDefaults {
 background-color: white;
 color: black;
 text-decoration: none;
 direction: ltr;
 text-transform: none;
 text-indent: 0;
 letter-spacing: 0;
 word-spacing: 0;
 white-space: pre-wrap;
 unicode-bidi: normal;
 background-image: none;
 text-shadow: none;
 list-style-image: none;
 list-style-type: none;
 padding: 0;
 margin: 0;
 border-collapse: collapse;
 vertical-align: bottom;
 font-style: normal;
 font-family: sans-serif;
 font-variant: normal;
 font-weight: normal;
 font-size: 10pt;
 text-align: right;
 table-layout: fixed;
 word-wrap: break-word;
 overflow-wrap: break-word;
 }
 .excelDefaults td {
 padding: 1px 5px;
 border: 1px solid silver;
 }
 .excelDefaults .style_02 {
 vertical-align: bottom;
 font-size: 14pt;
 border-left: none;
 border-right: none;
 border-top: none;
 border-bottom: none;
 }
 </style>
 */
@SuppressWarnings({"java:S106","java:S4823","java:S1192"})
public final class ExcelCellToHtml {
    private final Workbook wb;
    private final Sheet sheet;
    public static ExcelCellToHtml create(String filePath) throws IOException {
        Workbook wb = WorkbookFactory.create(new FileInputStream(filePath));
        return new ExcelCellToHtml(wb);
    }

    private ExcelCellToHtml(Workbook wb) {
        if (wb == null) {
            throw new NullPointerException("wb");
        }
        this.wb = wb;
        this.sheet = this.wb.getSheetAt(0);
    }

    private String getCellHtml(int rowNumber,int cellNumber){
        Row row = this.sheet.getRow(rowNumber);
        StringBuilder content = new StringBuilder("<table class=excelDefaults style=\"width:100%;\">\n" +
                "    <tr>\n" +
                "        <td class=style_02 style=\"text-align: left;\">");
        if(row!=null){
            Cell cell = row.getCell(cellNumber);
            if (cell != null) {
                if(cell.getCellType()!=CellType.NUMERIC) {
                    content.append(getCellValueAsHtml(sheet.getWorkbook(), cell));
                }
            }
        }
        content.append("</td>\n" +
                "    </tr>\n" +
                "</table>");
        return content.toString();
    }

    /**
     * Run this class as a program
     *
     * @param args The command line arguments.
     *
     * @throws Exception Exception we don't recover from.
     */
    public static void main(String[] args) throws Exception {
        ExcelCellToHtml toHtml = create("e:/t.xlsx");
        String html = toHtml.getCellHtml(1,0);
        System.out.println("html cell-->"+html);
        FileUtils.writeStringToFile(new File("e:/cell.html"),html,StandardCharsets.UTF_8);
    }

    private static String getCellValueAsHtml(Workbook workbook ,Cell cell) {
        StringBuilder cellValue = new StringBuilder();

        XSSFRichTextString xssfRichText = (XSSFRichTextString) cell.getRichStringCellValue();
        for (int i = 0; i < xssfRichText.numFormattingRuns(); i++) {
            int start = xssfRichText.getIndexOfFormattingRun(i);
            int end = xssfRichText.length();
            String textHead = "";
            if (i + 1 < xssfRichText.numFormattingRuns()) {
                end = xssfRichText.getIndexOfFormattingRun(i + 1);
            }
            String  textContent = xssfRichText.getString().substring(start, end);
            //XSSFRichTextString.numFormattingRuns()获取不到富文本头文本样式，这时候文本样式取单元格样式
            if(start == 0){
                Font font = workbook.getFontAt(cell.getCellStyle().getFontIndexAsInt());
                textContent = unionHtmlStyleFormFont(font, textContent, textHead);
            }else{
                XSSFFont font = xssfRichText.getFontAtIndex(start);
                textContent = unionHtmlStyleFormFont(font, textContent, textHead);
            }
            cellValue.append(textContent);
        }
        return cellValue.toString();
    }

    private static String unionHtmlStyleFormFont(Font font, String textContent, String textHead){
        if(ObjectUtils.isNotEmpty(font)){
            //加粗
            if(font.getBold()){
                textContent = "<strong>" + textContent + "</strong>";
            }
            //斜体
            if(font.getItalic()){
                textContent = "<em>" + textContent + "</em>";
            }
            //上下标
            switch (font.getTypeOffset()){
                case Font.SS_SUPER:
                    textContent = "<sup>" + textContent + "</sup>";
                    break;
                case Font.SS_SUB:
                    textContent = "<sub>" + textContent + "</sub>";
                default:
                    break;
            }
            //水平删除斜线
            if(font.getStrikeout()){
                textContent = "<span style=\"text-decoration: line-through;\">" + textContent + "</span>";
            }
            //下划线
            if(font.getUnderline() == Font.U_SINGLE){
                textContent = "<span style=\"text-decoration: underline;\">" + textContent + "</span>";
            }
        }
        if(StringUtils.isNotBlank(textHead)){
            textContent = textHead + textContent;
        }
        return textContent;
    }

}
